package com.scau.ums.util.dao;

import com.scau.ums.model.Repairmaterial;
import com.scau.ums.model.Repairresult;

public class RepairReportSql {
    public String getUnverifiedRepairReports(){
        String sql = "select * from `ums`.`repairReport` where state=1 ";
        return sql;
    }

    public String VerifySuccess(Integer RepairReportId){
        String sql = "UPDATE `ums`.`repairReport` SET state = 2 where id = "+RepairReportId;
        return sql;
    }
    //审核不通过从1状态变为8状态
    public String VerifyFailed(Integer RepairReportId,String remark){
        String sql = "UPDATE `ums`.`repairReport` SET state = 8,remark ='"+remark+ "' where id = "+RepairReportId;
        return sql;
    }

    public String FillRepairResult(Repairresult repairResult){
        String sql = "INSERT INTO `ums`.`repairResult`(`repairOrderId`,`labourCost`,`acceptorId`) VALUES(";
        sql = sql + "'"+repairResult.getRepairorderid()+"','"+repairResult.getLabourcost()+"','"+repairResult.getAcceptorid()+"');";
        return sql;
    }

    public String getRepairResultId(Integer repairOrderId){
        String sql = "Select id from `ums`.`repairResult` where repairOrderId="+repairOrderId;
        return sql;
    }


    public String FillRepairMaterial(Repairmaterial repairMaterial){
        String sql = "INSERT INTO `ums`.`repairMaterial`(`repairOrderId`,`name`,`number`,`cost`) VALUES(";
        sql = sql + "'"+repairMaterial.getRepairorderid()+"','"+repairMaterial.getName()+"','"+repairMaterial.getNumber()+"','"+repairMaterial.getCost()+"');";
        return sql;
    }

    public String CheckRepairResultExistence(Integer repairorderid){
        String sql = "Select * from `ums`.`repairResult` where repairOrderId="+repairorderid;
        return sql;
    }

    public String rewriteRepairResult(Repairresult repairResult){
        String sql = "UPDATE `ums`.`repairResult`  set labourCost = " + repairResult.getLabourcost();
        return sql;
    }

    public String UpdateRepairResultState(Integer repairReportId){
        String sql = "UPDATE `ums`.`repairReport` SET state=6 where id="+repairReportId;
        return sql;
    }

    public String getAllFinishedReport(Integer currentPage){
        String sql = "SELECT * from `ums`.`repairReport` where state=7";
        return sql;
    }

    public String getFinishedReportId(Integer type){
        String sql = "SELECT repairOrderId FROM `ums`.`repairResult`";
        return sql;
    }


    public String getFinishedReportIdsOfMonth(){
        String sql = "SELECT reportId from `ums`.`repairResult` join repairTaskOrder where timestampdiff(month,current_timestamp,finishTime)=0 group by repairTaskOrder.ID";
        return sql;
    }

    public String getFinishedReportIdsOfSeason(){
        String sql = "SELECT reportId from `ums`.`repairResult` join repairTaskOrder where timestampdiff(month,current_timestamp,finishTime)<=3 group by repairTaskOrder.ID";
        return sql;
    }

    public String getFinishedReportIdsOfYear(){
        String sql = "SELECT reportId from `ums`.`repairResult` join repairTaskOrder where timestampdiff(year,current_timestamp,finishTime)=0 group by repairTaskOrder.ID";
        return sql;
    }

    public String getCustomedFinishedReports(String whereClause){
        String sql="Select * from `ums`.`repairReport` where state=7 and "+whereClause;
        return sql;
    }

    public String getRepairTaskOrderIds(int start, int number){
        String sql="SELECT a.id from repairTaskOrder a join repairReport  b " +
                "where concat(IFNULL(a.id,''),IFNULL(a.reportId,''),IFNULL(a.maintainerId,''),IFNULL(a.maintainerName,''),IFNULL(a.maintainGroup,''),"+
                "IFNULL(b.id,''),IFNULL(b.reporterId,''),IFNULL(b.reporterName,''),IFNULL(b.reporterType,''),IFNULL(b.telephone,''),IFNULL(b.mail,''),IFNULL(b.dormRegion,''),IFNULL(b.dormBuilding,''),IFNULL(b.dormRoom,''),IFNULL(b.repairDesc,''),IFNULL(b.type,''),IFNULL(b.date,''),IFNULL(b.state,''),IFNULL(b.remark,''))"
                + " and a.reportId=b.id and b.state=5 "+ " limit " + start + "," + number +";";
        return sql;
    }

    public String getResultNumber(){
        String sql="SELECT count(*) from repairTaskOrder  a join repairReport  b " +
                "where concat(IFNULL(a.id,''),IFNULL(a.reportId,''),IFNULL(a.maintainerId,''),IFNULL(a.maintainerName,''),IFNULL(a.maintainGroup,''),"+
                "IFNULL(b.id,''),IFNULL(b.reporterId,''),IFNULL(b.reporterName,''),IFNULL(b.reporterType,''),IFNULL(b.telephone,''),IFNULL(b.mail,''),IFNULL(b.dormRegion,''),IFNULL(b.dormBuilding,''),IFNULL(b.dormRoom,''),IFNULL(b.repairDesc,''),IFNULL(b.type,''),IFNULL(b.date,''),IFNULL(b.state,''),IFNULL(b.remark,''))"
                + " and a.reportId=b.id and b.state=5 " ;
        return sql;
    }

    public String getStuRepairReportTypeNum(Integer type){
        String sql="SELECT COUNT(*) FROM `ums`.`repairReport` where reporterType='学生' and type="+type;
        return sql;
    }

    public String getTeaRepairReportTypeNum(Integer type){
        String sql="SELECT COUNT(*) FROM `ums`.`repairReport` where reporterType='老师' and type="+type;
        return sql;
    }

    public String getMaterialNumber(String searchKeyword){
        String sql = new String();
        sql = "select count(*) from repairTaskOrder where concat(IFNULL(id,''),IFNULL(reportId,''),IFNULL(maintainerId,''),IFNULL(maintainerName,''),IFNULL(maintainGroup,''))" +
               " like '%" + searchKeyword + "%';";
        return sql;
    }
    public String getTaskMaterialListByLimit(int start, int number, String searchKeyword){
        String sql = new String();
        sql = "select * from `ums`.`repairTaskOrder` where concat(IFNULL(id,''),IFNULL(reportId,''),IFNULL(maintainerId,''),IFNULL(maintainerName,''),IFNULL(maintainGroup,''))"+
                " like '%" + searchKeyword + "%'" +
                " limit " + start + "," + number + ";";
        return sql;
    }

    public String getLikeNumber(){
        String sql="SELECT COUNT(*) FROM repairEvaluation where evaluation='满意'";
        return sql;
    }

    public String getUnLikeNumber(){
        String sql="SELECT COUNT(*) FROM repairEvaluation where evaluation='不满意'";
        return sql;
    }


    public String getSomeoneLikeNumber(Integer maintainerId){
        String sql="select count(*) from repairEvaluation  join repairTaskOrder  where evaluation='满意' and reportId=repairReportId and maintainerId="+maintainerId;
        return sql;
    }

    public String getSomeoneUnLikeNumber(Integer maintainerId){
        String sql="select count(*) from repairEvaluation  join repairTaskOrder  where evaluation='不满意' and reportId=repairReportId and maintainerId="+maintainerId;
        return sql;
    }
}
